package com.ruoyi.web.controller.system;

import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.page.TableDataInfo;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.quartz.service.IYmdCgBaseService;
import com.ruoyi.system.domain.*;
import com.ruoyi.system.domain.vo.*;
import com.ruoyi.system.service.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

@RestController
@RequestMapping("/system/importcg")
public class SysImportCgController extends BaseController {

    @Autowired
    private ISysCgYjgZzService zzService;

    @Autowired
    private ISysCgYjgZmService zmService;

    @Autowired
    private ISysCgCpDimService dimService;

    @Autowired
    private ISysYjgKcService kcService;

    @Autowired
    private ISysCgImportMainService importMainService;

    @Autowired
    private ISysYmdCgBaseService ymdCgBaseService;

    @Autowired
    private ISysCgZqbcMainService zqbcMainService;

    @Autowired
    private ISysCgzqImportService zqImportService;

    @Autowired
    private ISysCgImportMainDetailService mainDetailService;

    @PostMapping("/export1276List")
    public void export1276List(HttpServletResponse response, YjgBcVo vo) {
        String sql1276 = "select a.*,IFNULL(b.arrsum,0)-a.zmqty as qhqty  from (select spec,realproductcode,mem_code,cg_name_cn,yongyou_name,sum(zm_nums) as zmqty from dw.cg_import_main where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' and spec='1276穴' group by 1,2,3,4,5 order by zmqty desc) a left join (select spec,mem_code,sum(arr_zm_nums) arrsum  from dw.cg_yjg_zm where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' group by 1,2) b on a.spec = b.spec and a.mem_code = b.mem_code";
        List<Map<String,Object>> list1276 = importMainService.excuteQuery(sql1276);
        List<YjgExport> list = new ArrayList<>();
        if (list1276.size() > 0) {
            for (Map<String, Object> map : list1276) {
                YjgExport yjgExport = new YjgExport();
                yjgExport.setSpec(map.get("spec").toString());
                yjgExport.setRealproductcode(map.get("realproductcode").toString());
                yjgExport.setMem_code(map.get("mem_code").toString());
                yjgExport.setCg_name_cn(map.get("cg_name_cn").toString());
                yjgExport.setYongyou_name(map.get("yongyou_name").toString());
                yjgExport.setZmqty(map.get("zmqty").toString()+"盘");
                yjgExport.setQhqty(map.get("qhqty").toString()+"盘");

                list.add(yjgExport);
            }
        }
        ExcelUtil<YjgExport> util = new ExcelUtil<YjgExport>(YjgExport.class);
        util.exportExcel(response, list, "洋桔梗种苗未满足-1276穴-采购进度");
    }

    @PostMapping("/export864List")
    public void export864List(HttpServletResponse response, YjgBcVo vo) {
        String sql864 = "select a.*,IFNULL(b.arrsum,0)-a.zmqty as qhqty  from (select spec,realproductcode,mem_code,cg_name_cn,yongyou_name,sum(zm_nums) as zmqty from dw.cg_import_main where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' and spec='864穴' group by 1,2,3,4,5 order by zmqty desc) a left join (select spec,mem_code,sum(arr_zm_nums) arrsum  from dw.cg_yjg_zm where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' group by 1,2) b on a.spec = b.spec and a.mem_code = b.mem_code";
        List<Map<String,Object>> list864 = importMainService.excuteQuery(sql864);
        List<YjgExport> list = new ArrayList<>();
        if (list864.size() > 0) {
            for (Map<String, Object> map : list864) {
                YjgExport yjgExport = new YjgExport();
                yjgExport.setSpec(map.get("spec").toString());
                yjgExport.setRealproductcode(map.get("realproductcode").toString());
                yjgExport.setMem_code(map.get("mem_code").toString());
                yjgExport.setCg_name_cn(map.get("cg_name_cn").toString());
                yjgExport.setYongyou_name(map.get("yongyou_name").toString());
                yjgExport.setZmqty(map.get("zmqty").toString()+"盘");
                yjgExport.setQhqty(map.get("qhqty").toString()+"盘");

                list.add(yjgExport);
            }
        }
        ExcelUtil<YjgExport> util = new ExcelUtil<YjgExport>(YjgExport.class);
        util.exportExcel(response, list, "洋桔梗种苗未满足-864穴-采购进度");
    }

    @PostMapping("/exportBcList")
    public void exportBcList(HttpServletResponse response, YjgBcVo vo) {
        String sqlbc = "select  a.*,sum(zmqty) over(partition by a.fh_date,a.realproductcode order by a.realproductcode) as lack " +
                "from (select  " +
                "case agentid_name when '洋丽人' then'洋丽人' " +
                "when '何文丽' then '何文丽' " +
                "WHEN  '王媛' then '王媛' else '上云丰' end as cus_cls, " +
                "agentid_name, " +
                " fh_date, " +
                " qd_date, " +
                " mem_code, " +
                " realproductcode, " +
                " yongyou_name, " +
                " cg_name_cn, " +
                " spec, " +
                " sum(zm_nums) zmqty, " +
                " sum(zz_nums) zzqty " +
                "from " +
                "dw.cg_import_main " +
                "where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"'" +
                " group by  " +
                "1,2,3,4,5,6,7,8,9) a  " +
                "order by a.fh_date desc";
        List<Map<String,Object>> listBc = importMainService.excuteQuery(sqlbc);

        List<YjgBcExport> list = new ArrayList<>();
        if (listBc.size() > 0) {
            for (Map<String, Object> map : listBc) {
                YjgBcExport yjgExport = new YjgBcExport();
                yjgExport.setFh_date(map.get("fh_date").toString());
                yjgExport.setSpec(map.get("spec").toString());
                yjgExport.setCus_cls(map.get("cus_cls").toString());
                yjgExport.setRealproductcode(map.get("realproductcode").toString());
                yjgExport.setYongyou_name(map.get("yongyou_name").toString());
                yjgExport.setCg_name_cn(map.get("cg_name_cn").toString());
                yjgExport.setMem_code(map.get("mem_code").toString());
                yjgExport.setAgentid_name(map.get("agentid_name").toString());
                yjgExport.setQd_date(map.get("qd_date").toString());
                yjgExport.setZmqty(map.get("zmqty").toString()+"盘");
                yjgExport.setZzqty(map.get("zzqty").toString());
                yjgExport.setLack(map.get("lack").toString()+"盘");
                list.add(yjgExport);
            }
        }
        ExcelUtil<YjgBcExport> util = new ExcelUtil<YjgBcExport>(YjgBcExport.class);
        util.exportExcel(response, list, "洋桔梗种苗未满足-需补采订单");
    }

    @PostMapping("/exportDyList")
    public void exportDyList(HttpServletResponse response, YjgBcVo vo) {
        String sqldy = "select * from dw.import_unnecessary_zm where fh_date>='"+vo.getStartFhDate()+"' and fh_date<='"+vo.getEndFhDate()+"' order by fh_date desc";
        List<Map<String,Object>> listRedundant = importMainService.excuteQuery(sqldy);
        List<YjgDyExport> list = new ArrayList<>();
        if (listRedundant.size() > 0) {
            for (Map<String, Object> map : listRedundant) {
                YjgDyExport yjgExport = new YjgDyExport();
                yjgExport.setFh_date(map.get("fh_date").toString());
                yjgExport.setCus_type(map.get("cus_type").toString());
                yjgExport.setYongyou_code(map.get("yongyou_code").toString());
                yjgExport.setYongyou_name(map.get("yongyou_name").toString());
                yjgExport.setCp_en_name(map.get("cp_en_name").toString());
                yjgExport.setQtys(map.get("qtys").toString()+"盘");
                list.add(yjgExport);
            }
        }
        ExcelUtil<YjgDyExport> util = new ExcelUtil<YjgDyExport>(YjgDyExport.class);
        util.exportExcel(response, list, "洋桔梗种苗-已满足且多余订单");
    }

    @GetMapping("/listYjgBcSummary")
    public AjaxResult listYjgBcSummary(YjgBcVo vo){
        String sql1276 = "select a.*,IFNULL(b.arrsum,0)-a.zmqty as qhqty  from (select spec,realproductcode,mem_code,cg_name_cn,yongyou_name,sum(zm_nums) as zmqty from dw.cg_import_main where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' and spec='1276穴' group by 1,2,3,4,5 order by zmqty desc) a left join (select spec,mem_code,sum(arr_zm_nums) arrsum  from dw.cg_yjg_zm where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' group by 1,2) b on a.spec = b.spec and a.mem_code = b.mem_code";
        List<Map<String,Object>> list1276 = importMainService.excuteQuery(sql1276);

        String sql1014 = "select a.*,IFNULL(b.arrsum,0)-a.zmqty as qhqty  from (select spec,realproductcode,mem_code,cg_name_cn,yongyou_name,sum(zm_nums) as zmqty from dw.cg_import_main where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' and spec='1014穴' group by 1,2,3,4,5 order by zmqty desc) a left join (select spec,mem_code,sum(arr_zm_nums) arrsum  from dw.cg_yjg_zm where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' group by 1,2) b on a.spec = b.spec and a.mem_code = b.mem_code";
        List<Map<String,Object>> list1014 = importMainService.excuteQuery(sql1014);

        String sql864 = "select a.*,IFNULL(b.arrsum,0)-a.zmqty as qhqty  from (select spec,realproductcode,mem_code,cg_name_cn,yongyou_name,sum(zm_nums) as zmqty from dw.cg_import_main where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' and spec='864穴' group by 1,2,3,4,5 order by zmqty desc) a left join (select spec,mem_code,sum(arr_zm_nums) arrsum  from dw.cg_yjg_zm where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"' group by 1,2) b on a.spec = b.spec and a.mem_code = b.mem_code";
        List<Map<String,Object>> list864 = importMainService.excuteQuery(sql864);

        String sqlbc = "select  a.*,sum(zmqty) over(partition by a.fh_date,a.realproductcode order by a.realproductcode) as lack " +
                "from (select  " +
                "case agentid_name when '洋丽人' then'洋丽人' " +
                "when '何文丽' then '何文丽' " +
                "WHEN  '王媛' then '王媛' else '上云丰' end as cus_cls, " +
                "agentid_name, " +
                " fh_date, " +
                " qd_date, " +
                " mem_code, " +
                " realproductcode, " +
                " yongyou_name, " +
                " cg_name_cn, " +
                " spec, " +
                " sum(zm_nums) zmqty, " +
                " sum(zz_nums) zzqty " +
                "from " +
                "dw.cg_import_main " +
                "where DATE(fh_date)>='"+vo.getStartFhDate()+"' and DATE(fh_date)<='"+vo.getEndFhDate()+"'" +
                " group by  " +
                "1,2,3,4,5,6,7,8,9) a  " +
                "order by a.fh_date desc";
        List<Map<String,Object>> listBc = importMainService.excuteQuery(sqlbc);

        String sqldy = "select * from dw.import_unnecessary_zm where fh_date>='"+vo.getStartFhDate()+"' and fh_date<='"+vo.getEndFhDate()+"' order by fh_date desc";
        List<Map<String,Object>> listRedundant = importMainService.excuteQuery(sqldy);
        Map<String,Object> res = new HashMap<>();
        res.put("list1276",list1276);
        res.put("list1014",list1014);
        res.put("list864",list864);
        res.put("listBc",listBc);
        res.put("listRedundant",listRedundant);
        return AjaxResult.success(res);
    }

    @GetMapping("/listInventory")
    public AjaxResult listInventory(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
//        List<Map<String,Object>> list = importMainService.selectInventoryList(year);
        List<Map<String,Object>> list = importMainService.selectInventoryListNew();
        if(list.size()>0){
            for(Map<String,Object> map:list){
                if(map.get("dq_kc")!=null){
                    BigDecimal dq_kc = new BigDecimal(map.get("dq_kc").toString());
                    BigDecimal cents = dq_kc.divide(new BigDecimal(1000), 1, BigDecimal.ROUND_HALF_UP);
                    map.put("cents",cents);
                }
                map.put("cent1276",map.get("t1276"));
                map.put("cent1014",map.get("t1014"));
                map.put("cent864",map.get("t864"));
            }
        }
        return AjaxResult.success(list);
    }

    @GetMapping("/listUnfulfilled")
    public TableDataInfo listUnfulfilled(SysCgZqbcMain param){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date())+"-01-01";
        startPage();
        List<Map<String,Object>> list = importMainService.selectUnfulfilledList(year);
        return getDataTable(list);
    }

    @PostMapping("/exportInventoryList")
    public void exportInventoryList(HttpServletResponse response){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
//        List<Map<String,Object>> list = importMainService.selectInventoryList(year);
        List<Map<String,Object>> list = importMainService.selectInventoryListNew();
        List<YjgInventoryVo> res = new ArrayList<>();
        if(list.size()>0){
            for(Map<String,Object> map:list){
                YjgInventoryVo vo = new YjgInventoryVo();
                if(map.get("sup_cn_name")!=null){
                    vo.setSup_cn_name(map.get("sup_cn_name").toString());
                }
                if(map.get("sup_en_name")!=null){
                    vo.setSup_en_name(map.get("sup_en_name").toString());
                }
                if(map.get("cg_name_cn")!=null){
                    vo.setCg_name_cn(map.get("cg_name_cn").toString());
                }
                if(map.get("cp_en_name")!=null){
                    vo.setCp_en_name(map.get("cp_en_name").toString());
                }
                if(map.get("mem_code")!=null){
                    vo.setMem_code(map.get("mem_code").toString());
                }
                if(map.get("dq_kc")!=null){
                    vo.setDq_kc(map.get("dq_kc").toString().replaceAll("\\.0",""));
                }
                if(map.get("t1276")!=null){
                    vo.setT1276(map.get("t1276").toString());
                }
                if(map.get("t1014")!=null){
                    vo.setT1014(map.get("t1014").toString());
                }
                if(map.get("t864")!=null){
                    vo.setT864(map.get("t864").toString());
                }
                res.add(vo);
            }
        }
        ExcelUtil<YjgInventoryVo> util = new ExcelUtil<YjgInventoryVo>(YjgInventoryVo.class);
        util.exportExcel(response, res, "洋桔梗-多余种子去库存");
    }

    @PostMapping("/exportUnfulfilledList")
    public void exportUnfulfilledList(HttpServletResponse response){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date())+"-01-01";
        List<Map<String,Object>> list = importMainService.selectUnfulfilledList(year);
        List<UnfulfilledVo> res = new ArrayList<>();
        if(list.size()>0){
            for(Map<String,Object> map : list){
                UnfulfilledVo vo = new UnfulfilledVo();
                vo.setFh_date_label(map.get("fh_date_label").toString());
                vo.setMem_code(map.get("mem_code").toString());
                vo.setYongyou_name(map.get("yongyou_name").toString());
                vo.setYongyou_htcode(map.get("yongyou_htcode").toString());
                vo.setHt_date(map.get("ht_date").toString());
                vo.setSale_name(map.get("sale_name").toString());
                vo.setDepartment(map.get("department").toString());
                vo.setCustomer_name(map.get("customer_name").toString());
                vo.setNums(map.get("nums").toString());
                vo.setUnit_name("盘");
                res.add(vo);
            }
        }
        ExcelUtil<UnfulfilledVo> util = new ExcelUtil<UnfulfilledVo>(UnfulfilledVo.class);
        util.exportExcel(response, res, "洋桔梗-未满足影响的订单");
    }

    @GetMapping("/listResampling")
    public TableDataInfo listResampling(SysCgZqbcMain param){
        startPage();
        List<Map<String,Object>> list = importMainService.selectResampleList();
        return getDataTable(list);
    }

    @GetMapping("/listInventoryDetail")
    public TableDataInfo listInventoryDetail(SysCgZqbcMain param){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date())+"-01-01";
        startPage();
        List<Map<String,Object>> list = importMainService.selectInventoryDetail(year);
        return getDataTable(list);
    }

    @PostMapping("/exportInventoryDetailList")
    public void exportInventoryDetailList(HttpServletResponse response) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date())+"-01-01";
        List<Map<String,Object>> list = importMainService.selectInventoryDetail(year);
        List<YjgInventoryDetail> res = new ArrayList<>();
        if (list.size()>0){
            for(Map<String,Object> map : list){
                YjgInventoryDetail vo = new YjgInventoryDetail();
                if(map.get("consigntime")!=null){
                    vo.setConsigntime(map.get("consigntime").toString());
                }
                if(map.get("sup_cn_name")!=null){
                    vo.setSup_cn_name(map.get("sup_cn_name").toString());
                }
                if(map.get("sup_en_name")!=null){
                    vo.setSup_en_name(map.get("sup_en_name").toString());
                }
                if(map.get("cg_name_cn")!=null){
                    vo.setCg_name_cn(map.get("cg_name_cn").toString());
                }
                if(map.get("cp_en_name")!=null){
                    vo.setCp_en_name(map.get("cp_en_name").toString());
                }
                if(map.get("mem_code")!=null){
                    vo.setMem_code(map.get("mem_code").toString());
                }
                if(map.get("dq_kc")!=null){
                    vo.setDq_kc(map.get("dq_kc").toString().replaceAll("\\.0",""));
                }
                res.add(vo);
            }
        }
        ExcelUtil<YjgInventoryDetail> util = new ExcelUtil<YjgInventoryDetail>(YjgInventoryDetail.class);
        util.exportExcel(response, res, "洋桔梗-多余库存-明细数据");
    }

    @PostMapping("/exportResamplingList")
    public void exportResamplingList(HttpServletResponse response){
        List<Map<String,Object>> list = importMainService.selectResampleList();
        List<ResamplingVo> res = new ArrayList<>();
        if(list!=null&&list.size()>0){
            for(Map<String,Object> map:list){
                ResamplingVo vo = new ResamplingVo();
                vo.setConsigntime(map.get("consigntime").toString());
                vo.setMem_code(map.get("mem_code").toString());
                vo.setCg_name_cn(map.get("cg_name_cn").toString());
                vo.setCp_en_name(map.get("cp_en_name").toString());
                vo.setSup_cn_name(map.get("sup_cn_name").toString());
                vo.setSup_en_name(map.get("sup_en_name").toString());
                vo.setZz_code(map.get("zz_code").toString());
                vo.setDq_kc(map.get("dq_kc").toString());
                vo.setT1276(map.get("t1276").toString());
                vo.setT1014(map.get("t1014").toString());
                vo.setT864(map.get("t864").toString());
                res.add(vo);
            }
        }
        ExcelUtil<ResamplingVo> util = new ExcelUtil<ResamplingVo>(ResamplingVo.class);
        util.exportExcel(response, res, "洋桔梗-需要补采-发货维度数据");
    }

    @GetMapping("/listUnsupply")
    public AjaxResult listUnsupply(){
        List<Map<String,Object>> list = importMainService.selectUnSupplyList();
        if(list!=null && list.size()>0){
            for(Map<String,Object> map:list){
                if(map.get("dq_kc") != null){
                    String dqkc = map.get("dq_kc").toString().replaceAll("-","");
                    BigDecimal dq_kc = new BigDecimal(dqkc);
                    BigDecimal dqkcn = dq_kc.divide(new BigDecimal(10000),2,BigDecimal.ROUND_HALF_UP);
                    map.put("cent",dqkcn);
                }else{
                    map.put("cent",0);
                }
            }
        }
        Map<String,Object> latest = importMainService.selectLatestDate();
        Map<String,Object> res = new HashMap<>();
        res.put("list",list);
        res.put("latest",latest);
        return AjaxResult.success(res);
    }

    @GetMapping("/listCgzqImport")
    public AjaxResult listCgzqImport() {
        List<Map<String,Object>> list = zqImportService.selectCgzqImport();
        List<SysCgzqImport> res = new ArrayList<>();
        if(list.size()>0){
            for(Map<String,Object> map:list){
                SysCgzqImport cgzq = new SysCgzqImport();
                if(map.get("brand")!=null){
                    cgzq.setBrand(map.get("brand").toString());
                }
                if(map.get("spec")!=null){
                    cgzq.setSpec(map.get("spec").toString());
                }
                if(map.get("pcode")!=null){
                    cgzq.setPcode(map.get("pcode").toString());
                }
                if(map.get("cname")!=null){
                    cgzq.setCname(map.get("cname").toString());
                }
                if(map.get("ename")!=null){
                    cgzq.setEname(map.get("ename").toString());
                }
                if(map.get("qty")!=null){
                    cgzq.setQty(map.get("qty").toString());
                }
                if(map.get("changeqty")!=null){
                    cgzq.setChangeqty(map.get("changeqty").toString());
                }
                res.add(cgzq);
            }
        }
        return AjaxResult.success(res);
    }

    @PostMapping("/exportCgzq")
    public void exportCgzq(HttpServletResponse response, SysCgzqImport base) {
        List<Map<String,Object>> list = zqImportService.selectCgzqImport();
        List<SysCgzqImport> res = new ArrayList<>();
        if(list.size()>0){
            for(Map<String,Object> map:list){
                SysCgzqImport cgzq = new SysCgzqImport();
                if(map.get("brand")!=null){
                    cgzq.setBrand(map.get("brand").toString());
                }
                if(map.get("spec")!=null){
                    cgzq.setSpec(map.get("spec").toString());
                }
                if(map.get("pcode")!=null){
                    cgzq.setPcode(map.get("pcode").toString());
                }
                if(map.get("cname")!=null){
                    cgzq.setCname(map.get("cname").toString());
                }
                if(map.get("ename")!=null){
                    cgzq.setEname(map.get("ename").toString());
                }
                if(map.get("qty")!=null){
                    cgzq.setQty(map.get("qty").toString());
                }
                if(map.get("changeqty")!=null){
                    cgzq.setChangeqty(map.get("changeqty").toString());
                }
                res.add(cgzq);
            }
        }
        ExcelUtil<SysCgzqImport> util = new ExcelUtil<SysCgzqImport>(SysCgzqImport.class);
        util.exportExcel(response, res, "种球类返表导入历史数据");
    }

    @GetMapping("/getBrandsListAccept")
    public AjaxResult getBrandsListAccept() {
        List<Map<String,Object>> brands = new ArrayList<>();
        Map<String,Object> brand1 = new HashMap<>();
        brand1.put("value","all");
        brand1.put("label","(全部)");
        brands.add(brand1);

        Map<String,Object> brand2 = new HashMap<>();
        brand2.put("value","Null");
        brand2.put("label","Null");
        brands.add(brand2);

        List<Map<String,Object>> brandsList = zqbcMainService.selectCgZqbcBrandsAcccpt();
        if(brandsList.size()>0){
            for (Map<String,Object> brand : brandsList) {
                if(brand!=null){
                    brands.add(brand);
                }
            }
        }
        return AjaxResult.success(brands);
    }

    @GetMapping("/getBrandsList")
    public AjaxResult getBrandsList() {
        List<Map<String,Object>> brands = new ArrayList<>();
        Map<String,Object> brand1 = new HashMap<>();
        brand1.put("value","all");
        brand1.put("label","(全部)");
        brands.add(brand1);

        Map<String,Object> brand2 = new HashMap<>();
        brand2.put("value","Null");
        brand2.put("label","Null");
        brands.add(brand2);

        List<Map<String,Object>> brandsList = zqbcMainService.selectCgZqbcBrands();
        if(brandsList.size()>0){
            for (Map<String,Object> brand : brandsList) {
                if(brand!=null){
                    brands.add(brand);
                }
            }
        }
        return AjaxResult.success(brands);
    }

    @GetMapping("/getZqDetailList")
    public AjaxResult getZqDetailList(SysYmdCgBase base) {
        SysYmdCgBase param = new SysYmdCgBase();
        param.setFhDateStart(base.getFhDateStart());
        param.setFhDateEnd(base.getFhDateEnd());
        if(base.getAgentidName()!=null){
            param.setAgentidName(base.getAgentidName());
        }
        if(base.getDpt()!=null){
            if(!base.getDpt().equalsIgnoreCase("all")){
                param.setDpt(base.getDpt());
            }
        }
        if(base.getSalename()!=null){
            if(!base.getSalename().equalsIgnoreCase("all")){
                param.setSalename(base.getSalename());
            }
        }
        List<SysYmdCgBase> list = ymdCgBaseService.selectSysYmdCgBaseList(param);
        return AjaxResult.success(list);
    }

    @PostMapping("/exportLinked")
    public void exportLinked(HttpServletResponse response, SysYmdCgBase base) {
        SysYmdCgBase param = new SysYmdCgBase();
        param.setFhDateStart(base.getFhDateStart());
        param.setFhDateEnd(base.getFhDateEnd());
        if(base.getAgentidName()!=null){
            param.setAgentidName(base.getAgentidName());
        }
        if(base.getDpt()!=null){
            if(!base.getDpt().equalsIgnoreCase("all")){
                param.setDpt(base.getDpt());
            }
        }
        if(base.getSalename()!=null){
            if(!base.getSalename().equalsIgnoreCase("all")){
                param.setSalename(base.getSalename());
            }
        }
        List<SysYmdCgBase> list = ymdCgBaseService.selectSysYmdCgBaseList(param);
        if(list.size()>0){
            for(SysYmdCgBase ymdCgBase:list){
                ymdCgBase.setSubqty(ymdCgBase.getSubqty().replaceAll("\\.0",""));
            }
        }
        ExcelUtil<SysYmdCgBase> util = new ExcelUtil<SysYmdCgBase>(SysYmdCgBase.class);
        util.exportExcel(response, list, "已维护—销售订单参考数据");
    }

    @PostMapping("/exportCgAllDetailList")
    public void exportCgAllDetailList(HttpServletResponse response, SysYmdCgBase base) {
        SysYmdCgBase param = new SysYmdCgBase();
        param.setFhDateStart(base.getFhDateStart());
        param.setFhDateEnd(base.getFhDateEnd());
        if(base.getAgentidName()!=null){
            param.setAgentidName(base.getAgentidName());
        }
        if(base.getDpt()!=null){
            if(!base.getDpt().equalsIgnoreCase("all")){
                param.setDpt(base.getDpt());
            }
        }
        if(base.getSalename()!=null){
            if(!base.getSalename().equalsIgnoreCase("all")){
                param.setSalename(base.getSalename());
            }
        }
        List<SysYmdCgBase> list = ymdCgBaseService.selectSysYmdCgBaseList(param);
        List<SysYmdCgBaseShort> res = new ArrayList<>();
        if(list.size()>0){
            for(SysYmdCgBase ymd:list){
                SysYmdCgBaseShort baseShort = new SysYmdCgBaseShort();
                baseShort.setPredate(ymd.getPredate());
                baseShort.setBrand(ymd.getBrand());
                baseShort.setXlname(ymd.getXlname());
                baseShort.setPcode(ymd.getPcode());
                baseShort.setPname(ymd.getPname());
                baseShort.setOrdercode(ymd.getOrdercode());
                baseShort.setPubuts(ymd.getPubuts());
                baseShort.setDpt(ymd.getDpt());
                baseShort.setSalename(ymd.getSalename());
                baseShort.setAgentidName(ymd.getAgentidName());
                if(ymd.getSubqty()!=null){
                    baseShort.setSubqty(ymd.getSubqty().replaceAll("\\.0",""));
                }
                res.add(baseShort);
            }
        }

        ExcelUtil<SysYmdCgBaseShort> util = new ExcelUtil<SysYmdCgBaseShort>(SysYmdCgBaseShort.class);
        util.exportExcel(response, res, "受库存不足影响订单");
    }

    @PostMapping("/exportCgAllList")
    public void exportCgAllList(HttpServletResponse response, SysCgZqbcMain param1) {
        SysCgZqbcMain param = new SysCgZqbcMain();
        if(param1.getYyname()!=null){
            param.setYyname(param1.getYyname());
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        param.setFdate(year);
        List<SysCgZqbcMain> list = new ArrayList<>();
        if(param1.getBrand()!=null){
            if(param1.getBrand().equalsIgnoreCase("Null")){
                list = zqbcMainService.selectCgZqbcBrandNullList(param);
            }else{
                if(param1.getBrand().equalsIgnoreCase("all")){
                    list = zqbcMainService.selectCgZqbcAllList(param);
                }else{
                    param.setBrand(param1.getBrand());
                    list = zqbcMainService.selectCgZqbcAllList(param);
                }
            }
        }else{
            list = zqbcMainService.selectCgZqbcAllList(param);
        }
        if(list.size()>0){
            for (SysCgZqbcMain sysCgZqbcMain : list) {
                if(sysCgZqbcMain.getBcqty()!=null){
                    BigDecimal bcqty = new BigDecimal(sysCgZqbcMain.getBcqty().replaceAll("-",""));
                    BigDecimal bcqtyNum = new BigDecimal(0);
                    if(bcqty.compareTo(new BigDecimal(0))>=0&&bcqty.compareTo(new BigDecimal("100"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(10),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(101))>=0&&bcqty.compareTo(new BigDecimal("1000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(100),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(1001))>=0&&bcqty.compareTo(new BigDecimal("10000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(10001))>=0&&bcqty.compareTo(new BigDecimal("100000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(100000))>0){
                        bcqtyNum = new BigDecimal(100);
                    }
                    sysCgZqbcMain.setBcqtyNum(bcqtyNum);
                    sysCgZqbcMain.setBcqty(sysCgZqbcMain.getBcqty().replaceAll("\\.0",""));

                }
            }
        }
        ExcelUtil<SysCgZqbcMain> util = new ExcelUtil<SysCgZqbcMain>(SysCgZqbcMain.class);
        util.exportExcel(response, list, "需采购数量数据");
    }

    @PostMapping("/exportAccept")
    public void exportAccept(HttpServletResponse response, SysCgZqbcMain param1) {
        SysCgZqbcMain param = new SysCgZqbcMain();
        if(param1.getYyname()!=null){
            param.setYyname(param1.getYyname());
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        param.setFdate(year);
        List<SysCgZqbcMain> list = new ArrayList<>();
        if(param1.getBrand()!=null){
            if(param1.getBrand().equalsIgnoreCase("Null")){
                list = zqbcMainService.selectCgZqbcBrandNullListAccept(param);
            }else{
                if(param1.getBrand().equalsIgnoreCase("all")){
                    list = zqbcMainService.selectCgZqbcAllListAccept(param);
                }else{
                    param.setBrand(param1.getBrand());
                    list = zqbcMainService.selectCgZqbcAllListAccept(param);
                }
            }
        }else{
            list = zqbcMainService.selectCgZqbcAllListAccept(param);
        }
        if(list.size()>0){
            for (SysCgZqbcMain sysCgZqbcMain : list) {
                if(sysCgZqbcMain.getBcqty()!=null){
                    BigDecimal bcqty = new BigDecimal(sysCgZqbcMain.getBcqty().replaceAll("-",""));
                    BigDecimal bcqtyNum = new BigDecimal(0);
                    if(bcqty.compareTo(new BigDecimal(0))>=0&&bcqty.compareTo(new BigDecimal("100"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(10),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(101))>=0&&bcqty.compareTo(new BigDecimal("1000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(100),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(1001))>=0&&bcqty.compareTo(new BigDecimal("10000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(10001))>=0&&bcqty.compareTo(new BigDecimal("100000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(100000))>0){
                        bcqtyNum = new BigDecimal(100);
                    }
                    sysCgZqbcMain.setBcqtyNum(bcqtyNum);
                    sysCgZqbcMain.setBcqty(sysCgZqbcMain.getBcqty().replaceAll("\\.0",""));

                }
            }
        }
        List<SysCgZqbcMainShort> res = new ArrayList<>();
        if(list.size()>0){
            for (SysCgZqbcMain sysCgZqbcMain : list) {
                SysCgZqbcMainShort sub = new SysCgZqbcMainShort();
                sub.setSpec(sysCgZqbcMain.getSpec());
                sub.setBrand(sysCgZqbcMain.getBrand());
                sub.setCpEnName(sysCgZqbcMain.getCpEnName());
                sub.setPcode(sysCgZqbcMain.getPcode());
                sub.setYyname(sysCgZqbcMain.getYyname());
                sub.setCgQty(sysCgZqbcMain.getCgQty());
                sub.setSqty(sysCgZqbcMain.getSqty());
                sub.setBcqty(sysCgZqbcMain.getBcqty());
                res.add(sub);
            }
        }
        ExcelUtil<SysCgZqbcMainShort> util = new ExcelUtil<SysCgZqbcMainShort>(SysCgZqbcMainShort.class);
        util.exportExcel(response, res, "剩余可接单量库存明细");
    }

    @GetMapping("/listAcceptList")
    public AjaxResult listAcceptList(SysCgZqbcMain param1) {
        SysCgZqbcMain param = new SysCgZqbcMain();
        if(param1.getYyname()!=null){
            param.setYyname(param1.getYyname());
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        param.setFdate(year);
        List<SysCgZqbcMain> list = new ArrayList<>();
        if(param1.getBrand()!=null){
            if(param1.getBrand().equalsIgnoreCase("Null")){
                list = zqbcMainService.selectCgZqbcBrandNullListAccept(param);
            }else{
                if(param1.getBrand().equalsIgnoreCase("all")){
                    list = zqbcMainService.selectCgZqbcAllListAccept(param);
                }else{
                    param.setBrand(param1.getBrand());
                    list = zqbcMainService.selectCgZqbcAllListAccept(param);
                }
            }
        }else{
            list = zqbcMainService.selectCgZqbcAllListAccept(param);
        }
        if(list.size()>0){
            for (SysCgZqbcMain sysCgZqbcMain : list) {
                if(sysCgZqbcMain.getBcqty()!=null){
                    BigDecimal bcqty = new BigDecimal(sysCgZqbcMain.getBcqty().replaceAll("-",""));
                    BigDecimal bcqtyNum = new BigDecimal(0);
                    if(bcqty.compareTo(new BigDecimal(0))>=0&&bcqty.compareTo(new BigDecimal("100"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(10),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(101))>=0&&bcqty.compareTo(new BigDecimal("1000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(100),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(1001))>=0&&bcqty.compareTo(new BigDecimal("10000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(10001))>=0&&bcqty.compareTo(new BigDecimal("100000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(100000))>0){
                        bcqtyNum = new BigDecimal(100);
                    }
                    sysCgZqbcMain.setBcqtyNum(bcqtyNum);
                    sysCgZqbcMain.setBcqty(sysCgZqbcMain.getBcqty().replaceAll("\\.0",""));
                }
            }
        }
        return AjaxResult.success(list);
    }

    @GetMapping("/listCgAll")
    public AjaxResult listCgAll(SysCgZqbcMain param1) {
        SysCgZqbcMain param = new SysCgZqbcMain();
        if(param1.getYyname()!=null){
            param.setYyname(param1.getYyname());
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        param.setFdate(year);
        List<SysCgZqbcMain> list = new ArrayList<>();
        if(param1.getBrand()!=null){
            if(param1.getBrand().equalsIgnoreCase("Null")){
                list = zqbcMainService.selectCgZqbcBrandNullList(param);
            }else{
                if(param1.getBrand().equalsIgnoreCase("all")){
                    list = zqbcMainService.selectCgZqbcAllList(param);
                }else{
                    param.setBrand(param1.getBrand());
                    list = zqbcMainService.selectCgZqbcAllList(param);
                }
            }
        }else{
            list = zqbcMainService.selectCgZqbcAllList(param);
        }
        if(list.size()>0){
            for (SysCgZqbcMain sysCgZqbcMain : list) {
                if(sysCgZqbcMain.getBcqty()!=null){
                    BigDecimal bcqty = new BigDecimal(sysCgZqbcMain.getBcqty().replaceAll("-",""));
                    BigDecimal bcqtyNum = new BigDecimal(0);
                    if(bcqty.compareTo(new BigDecimal(0))>=0&&bcqty.compareTo(new BigDecimal("100"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(10),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(101))>=0&&bcqty.compareTo(new BigDecimal("1000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(100),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(1001))>=0&&bcqty.compareTo(new BigDecimal("10000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(10001))>=0&&bcqty.compareTo(new BigDecimal("100000"))<=0){
                        bcqtyNum = bcqty.divide(new BigDecimal(1000),2,BigDecimal.ROUND_HALF_UP);
                    }else if (bcqty.compareTo(new BigDecimal(100000))>0){
                        bcqtyNum = new BigDecimal(100);
                    }
                    sysCgZqbcMain.setBcqtyNum(bcqtyNum);
                    sysCgZqbcMain.setBcqty(sysCgZqbcMain.getBcqty().replaceAll("\\.0",""));

                }
            }
        }
        return AjaxResult.success(list);
    }

    @GetMapping("/getInventoryList")
    public TableDataInfo getInventoryList(SysCgZqbcMain param) {
        startPage();
        List<SysCgZqbcMain> list = zqbcMainService.selectCgZqbcList(param);
        if(list.size()>0){
            for(SysCgZqbcMain zqbcMain:list){
                zqbcMain.setSqty(zqbcMain.getSqty().replaceAll("\\.0",""));
                zqbcMain.setBcqty(zqbcMain.getBcqty().replaceAll("\\.0",""));
            }
        }
        return getDataTable(list);
    }

    @PostMapping("/exportInventory")
    public void exportInventory(HttpServletResponse response, SysCgZqbcMain param) {
        List<SysCgZqbcMain> list = zqbcMainService.selectCgZqbcList(param);
        ExcelUtil<SysCgZqbcMain> util = new ExcelUtil<SysCgZqbcMain>(SysCgZqbcMain.class);
        util.exportExcel(response, list, "供应商预估库存不足明细表数据");
    }

    @GetMapping("/listCustomers")
    public AjaxResult listCustomers(SysCgImportMainDetail param) {
        List<Map<String,Object>> list = importMainService.selectCustomers(param.getFhDateStart(),param.getFhDateEnd());
        if(list.size()>0){
            for (Map<String,Object> map : list) {
                if(map.get("t1276")!=null){
                    if(!map.get("t1276").toString().equalsIgnoreCase("0.0")){
                        map.put("t1276", map.get("t1276"));
                    }else {
                        map.put("t1276", "");
                    }
                }

                if(map.get("sum")!=null){
                    if(!map.get("sum").toString().equalsIgnoreCase("0.0")){
                        map.put("sum", map.get("sum"));
                    }else {
                        map.put("sum", "");
                    }
                }

                if(map.get("t864")!=null){
                    if(!map.get("t864").toString().equalsIgnoreCase("0.0")){
                        map.put("t864", map.get("t864"));
                    }else {
                        map.put("t864", "");
                    }
                }
            }
        }
        return AjaxResult.success(list);
    }

    @PostMapping("/exportCustomer")
    public void exportCustomer(HttpServletResponse response, SysCgImportMainDetail param) {
        List<Map<String,Object>> list = importMainService.selectCustomers(param.getFhDateStart(),param.getFhDateEnd());
        List<SysCustomerExcel> res = new ArrayList<>();
        if(list.size()>0){
            for (Map<String,Object> map : list) {
                SysCustomerExcel specExcel = new SysCustomerExcel();
                specExcel.setRq(map.get("rq").toString());
                specExcel.setCustomer(map.get("customer").toString());
                if(map.get("t1276")!=null){
                    if(!map.get("t1276").toString().equalsIgnoreCase("0.0")){
                        map.put("t1276", map.get("t1276"));
                        specExcel.setT1276(map.get("t1276").toString().replaceAll("\\.0",""));
                    }else {
                        map.put("t1276", "");
                        specExcel.setT1276("");
                    }
                }
                if(map.get("sum")!=null){
                    if(!map.get("sum").toString().equalsIgnoreCase("0.0")){
                        map.put("sum", map.get("sum"));
                        specExcel.setSum(map.get("sum").toString().replaceAll("\\.0",""));
                    }else {
                        map.put("sum", "");
                        specExcel.setSum("");
                    }
                }
                if(map.get("t864")!=null){
                    if(!map.get("t864").toString().equalsIgnoreCase("0.0")){
                        map.put("t864", map.get("t864"));
                        specExcel.setT864(map.get("t864").toString().replaceAll("\\.0",""));
                    }else {
                        map.put("t864", "");
                        specExcel.setT864("");
                    }
                }
                res.add(specExcel);
            }
        }
        ExcelUtil<SysCustomerExcel> util = new ExcelUtil<SysCustomerExcel>(SysCustomerExcel.class);
        util.exportExcel(response, res, "洋桔梗种苗负责人参考数据");
    }

    @GetMapping("/listSpecs")
    public AjaxResult listSpecs(SysCgImportMainDetail param) {
        List<Map<String,Object>> list = importMainService.selectSpecs(param.getFhDateStart(),param.getFhDateEnd());
        if(list.size()>0){
            for (Map<String,Object> map : list) {
                if(map.get("t1276")!=null){
                    if(!map.get("t1276").toString().equalsIgnoreCase("0.0")){
                        map.put("t1276", map.get("t1276"));
                    }else {
                        map.put("t1276", "");
                    }
                }

                if(map.get("sum")!=null){
                    if(!map.get("sum").toString().equalsIgnoreCase("0.0")){
                        map.put("sum", map.get("sum"));
                    }else {
                        map.put("sum", "");
                    }
                }

                if(map.get("t864")!=null){
                    if(!map.get("t864").toString().equalsIgnoreCase("0.0")){
                        map.put("t864", map.get("t864"));
                    }else {
                        map.put("t864", "");
                    }
                }
            }
        }
        return AjaxResult.success(list);
    }

    @PostMapping("/exportSpecs")
    public void exportSpecs(HttpServletResponse response, SysCgImportMainDetail param) {
        List<Map<String,Object>> list = importMainService.selectSpecs(param.getFhDateStart(),param.getFhDateEnd());
        List<SysSpecExcel> res = new ArrayList<>();
        if(list.size()>0){
            for (Map<String,Object> map : list) {
                SysSpecExcel specExcel = new SysSpecExcel();
                specExcel.setRq(map.get("rq").toString());
                if(map.get("t1276")!=null){
                    if(!map.get("t1276").toString().equalsIgnoreCase("0.0")){
                        map.put("t1276", map.get("t1276"));
                        specExcel.setT1276(map.get("t1276").toString().replaceAll("\\.0",""));
                    }else {
                        map.put("t1276", "");
                        specExcel.setT1276("");
                    }
                }
                if(map.get("sum")!=null){
                    if(!map.get("sum").toString().equalsIgnoreCase("0.0")){
                        map.put("sum", map.get("sum"));
                        specExcel.setSum(map.get("sum").toString().replaceAll("\\.0",""));
                    }else {
                        map.put("sum", "");
                        specExcel.setSum("");
                    }
                }
                if(map.get("t864")!=null){
                    if(!map.get("t864").toString().equalsIgnoreCase("0.0")){
                        map.put("t864", map.get("t864"));
                        specExcel.setT864(map.get("t864").toString().replaceAll("\\.0",""));
                    }else {
                        map.put("t864", "");
                        specExcel.setT864("");
                    }
                }
                res.add(specExcel);
            }
        }
        ExcelUtil<SysSpecExcel> util = new ExcelUtil<SysSpecExcel>(SysSpecExcel.class);
        util.exportExcel(response, res, "洋桔梗种苗规格参考数据");
    }

    @PostMapping("/exportYjgCg")
    public void exportYjgCg(HttpServletResponse response, SysCgImportMainDetail param) {
        SysCgImportMain main = new SysCgImportMain();
        main.setFhDateStart(param.getFhDateStart());
        main.setFhDateEnd(param.getFhDateEnd());
        List<SysCgImportMain> list = importMainService.selectCgImportMainList(main);
        List<SysCgImportMainExcel> res = new ArrayList<>();
        ExcelUtil<SysCgImportMainExcel> util = new ExcelUtil<SysCgImportMainExcel>(SysCgImportMainExcel.class);
        if(list.size()>0){
            for(SysCgImportMain sub:list){
                SysCgImportMainExcel excel = new SysCgImportMainExcel();
                excel.setFhDate(sub.getFhDate());
                excel.setYongyouName(sub.getYongyouName());
                excel.setSpec(sub.getSpec());
                excel.setAgentidName(sub.getAgentidName());
                excel.setCgNameCn(sub.getCgNameCn());
                excel.setCpEnName(sub.getCpEnName());
                excel.setMemCode(sub.getMemCode());
                excel.setZmNums(sub.getZmNums());
                excel.setZzNums(sub.getZzNums());
                res.add(excel);
            }
        }
        util.exportExcel(response, res, "洋桔梗种苗—采购参考");
    }

    @GetMapping("/listYjgCg")
    public TableDataInfo listYjgCg(SysCgImportMainDetail param) {
        SysCgImportMain main = new SysCgImportMain();
        main.setFhDateStart(param.getFhDateStart());
        main.setFhDateEnd(param.getFhDateEnd());
        startPage();
        List<SysCgImportMain> list = importMainService.selectCgImportMainList(main);
        if(list.size()>0){
            for (SysCgImportMain cgmain : list) {
                if(!cgmain.getAgentidName().equalsIgnoreCase("洋丽人")&&!cgmain.getAgentidName().equalsIgnoreCase("何文丽")){
                    cgmain.setAgentidName("上云丰");
                }else{
                    cgmain.setAgentidName(cgmain.getAgentidName());
                }
            }
        }
        return getDataTable(list);
    }

    @GetMapping("/listCgMainDetails")
    public TableDataInfo listCgMainDetails(SysCgImportMainDetail param) {
        SysCgImportMainDetail detail = new SysCgImportMainDetail();
        if(param.getDepartment()!=null){
            if(!param.getDepartment().equals("all")){
                detail.setDepartment(param.getDepartment());
            }
        }
        if(param.getCustomerName()!=null){
            if(!param.getCustomerName().equals("all")){
                detail.setCustomerName(param.getCustomerName());
            }
        }
        if(param.getSaleName()!=null){
            if(!param.getSaleName().equals("all")){
                detail.setSaleName(param.getSaleName());
            }
        }
        if(param.getYongyouName()!=null){
            detail.setYongyouName(param.getYongyouName());
        }
        detail.setFhDateStart(param.getFhDateStart());
        detail.setFhDateEnd(param.getFhDateEnd());
        startPage();
        List<SysCgImportMainDetail> list = mainDetailService.selectCgImportDetailList(detail);
        if(list.size()>0){
            for(SysCgImportMainDetail mainDetail:list){
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                if(mainDetail.getHtDate()!=null){
                    mainDetail.setHtDateLabel(sdf.format(mainDetail.getHtDate()));
                }
            }
        }
        return getDataTable(list);
    }

    @PostMapping("/exportCgMainDetail")
    public void exportCgMainDetail(HttpServletResponse response, SysCgImportMainDetail param) {
        SysCgImportMainDetail detail = new SysCgImportMainDetail();
        if(param.getDepartment()!=null){
            if(!param.getDepartment().equals("all")){
                detail.setDepartment(param.getDepartment());
            }
        }
        if(param.getCustomerName()!=null){
            if(!param.getCustomerName().equals("all")){
                detail.setCustomerName(param.getCustomerName());
            }
        }
        if(param.getSaleName()!=null){
            if(!param.getSaleName().equals("all")){
                detail.setSaleName(param.getSaleName());
            }
        }
        if(param.getYongyouName()!=null){
            detail.setYongyouName(param.getYongyouName());
        }
        detail.setFhDateStart(param.getFhDateStart());
        detail.setFhDateEnd(param.getFhDateEnd());
        List<SysCgImportMainDetail> list = mainDetailService.selectCgImportDetailList(detail);
        if(list.size()>0){
            for(SysCgImportMainDetail mainDetail:list){
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                if(mainDetail.getHtDate()!=null){
                    mainDetail.setHtDateLabel(sdf.format(mainDetail.getHtDate()));
                }
            }
        }
        ExcelUtil<SysCgImportMainDetail> util = new ExcelUtil<SysCgImportMainDetail>(SysCgImportMainDetail.class);
        util.exportExcel(response, list, "洋桔梗-已维护品种-销售订单参考数据");
    }

    @GetMapping("/listLinked")
    public TableDataInfo listLinked(SysYmdCgBase param) {
        SysYmdCgBase main = new SysYmdCgBase();
        if(!param.getDpt().equalsIgnoreCase("all")){
            main.setDpt(param.getDpt());
        }
        if(!param.getSalename().equalsIgnoreCase("all")){
            main.setSalename(param.getSalename());
        }
        if (!param.getBrand().equalsIgnoreCase("all")&& !param.getBrand().equalsIgnoreCase("Null")) {
            main.setBrand(param.getBrand());
        }
        if(param.getAgentidName()!=null){
            main.setAgentidName(param.getAgentidName());
        }
        if(param.getPname()!=null){
            main.setPname(param.getPname());
        }
        main.setFhDateStart(param.getFhDateStart());
        main.setFhDateEnd(param.getFhDateEnd());
        startPage();
        List<SysYmdCgBase> list = ymdCgBaseService.selectYmdCgBaseLinkedList(main);
        if(list.size()>0){
            for(SysYmdCgBase ymdCgBase:list){
                ymdCgBase.setSubqty(ymdCgBase.getSubqty().replaceAll("\\.0",""));
            }
        }
        return getDataTable(list);
    }

    @GetMapping("/listUnLinked")
    public TableDataInfo listUnLinked(SysYmdCgBase param) {
        SysYmdCgBase main = new SysYmdCgBase();
        if(!param.getDpt().equalsIgnoreCase("all")){
            main.setDpt(param.getDpt());
        }
        if(!param.getSalename().equalsIgnoreCase("all")){
            main.setSalename(param.getSalename());
        }
        if (!param.getBrand().equalsIgnoreCase("all")&& !param.getBrand().equalsIgnoreCase("Null")) {
            main.setBrand(param.getBrand());
        }
        if(param.getAgentidName()!=null){
            main.setAgentidName(param.getAgentidName());
        }
        if(param.getPname()!=null){
            main.setPname(param.getPname());
        }
        main.setFhDateStart(param.getFhDateStart());
        main.setFhDateEnd(param.getFhDateEnd());
        startPage();
        List<SysYmdCgBase> list = ymdCgBaseService.selectYmdCgBaseUnLinkedList(main);
        if(list.size()>0){
            for (SysYmdCgBase sysYmdCgBase : list) {
                sysYmdCgBase.setSubqty(sysYmdCgBase.getSubqty().replaceAll("\\.0",""));
            }
        }
        return getDataTable(list);
    }

    @PostMapping("/exportUnLinked")
    public void exportUnLinked(HttpServletResponse response, SysYmdCgBase param) {
        SysYmdCgBase main = new SysYmdCgBase();
        if(!param.getDpt().equalsIgnoreCase("all")){
            main.setDpt(param.getDpt());
        }
        if(!param.getSalename().equalsIgnoreCase("all")){
            main.setSalename(param.getSalename());
        }
        if (!param.getBrand().equalsIgnoreCase("all")&& !param.getBrand().equalsIgnoreCase("Null")) {
            main.setBrand(param.getBrand());
        }
        if(param.getAgentidName()!=null){
            main.setAgentidName(param.getAgentidName());
        }
        if(param.getPname()!=null){
            main.setPname(param.getPname());
        }
        main.setFhDateStart(param.getFhDateStart());
        main.setFhDateEnd(param.getFhDateEnd());
        List<SysYmdCgBase> list = ymdCgBaseService.selectYmdCgBaseUnLinkedList(main);
        if(list!=null && list.size()>0){
            for(SysYmdCgBase sysYmdCgBase : list){
                sysYmdCgBase.setBrand("");
                sysYmdCgBase.setXlname("");
                sysYmdCgBase.setSubqty(sysYmdCgBase.getSubqty().replaceAll("\\.0",""));
            }
        }
        ExcelUtil<SysYmdCgBase> util = new ExcelUtil<SysYmdCgBase>(SysYmdCgBase.class);
        util.exportExcel(response, list, "已维护—销售订单参考数据");
    }

    @PostMapping("/getInitParams30")
    public AjaxResult getInitParams30(){
        Map<String,Object> res = new HashMap<>();
        List<Map<String,Object>> depts = new ArrayList<>();
        List<Map<String,Object>> sales = new ArrayList<>();
        Map<String,Object> subDept = new HashMap<>();
        subDept.put("value","all");
        subDept.put("label","(全部)");
        depts.add(subDept);
        Map<String,Object> subSal = new HashMap<>();
        subSal.put("value","all");
        subSal.put("label","(全部)");
        sales.add(subSal);
        res.put("depts", depts);
        res.put("sales", sales);
        SimpleDateFormat yearsdf = new SimpleDateFormat("yyyy");
        Date date = new Date();
        String year = yearsdf.format(date);
        List<Map<String,Object>> deptsInfo = ymdCgBaseService.selectDeptsByYear(year);
        List<Map<String,Object>> salesInfo = ymdCgBaseService.selectSalesByYear(year);
        if(deptsInfo.size()>0){
            for(Map<String,Object> dept:deptsInfo){
                depts.add(dept);
            }
        }
        if(salesInfo.size()>0){
            for(Map<String,Object> sal:salesInfo){
                sales.add(sal);
            }
        }
        return AjaxResult.success(res);
    }

    @PostMapping("/getQueryParams")
    public AjaxResult getQueryParams() {
        Map<String,Object> res = new HashMap<>();
        List<Map<String,Object>> depts = new ArrayList<>();
        List<Map<String,Object>> sales = new ArrayList<>();
        List<Map<String,Object>> customers = new ArrayList<>();
        Map<String,Object> subDept = new HashMap<>();
        subDept.put("value","all");
        subDept.put("label","(全部)");
        depts.add(subDept);
        Map<String,Object> subSal = new HashMap<>();
        subSal.put("value","all");
        subSal.put("label","(全部)");
        sales.add(subSal);
        Map<String,Object> subCus = new HashMap<>();
        subCus.put("value","all");
        subCus.put("label","(全部)");
        customers.add(subCus);
        SimpleDateFormat yearsdf = new SimpleDateFormat("yyyy");
        Date date = new Date();
        String year = yearsdf.format(date);
        List<Map<String,Object>> deptsInfo = mainDetailService.selectDepts(year);
        List<Map<String,Object>> salesInfo = mainDetailService.selectSales(year);
        List<Map<String,Object>> customerInfo = mainDetailService.selectCustomer(year);
        if(deptsInfo.size()>0){
            for(Map<String,Object> dept:deptsInfo){
                depts.add(dept);
            }
        }
        if(salesInfo.size()>0){
            for(Map<String,Object> sal:salesInfo){
                sales.add(sal);
            }
        }
        if(customerInfo.size()>0){
            for(Map<String,Object> customer:customerInfo){
                customers.add(customer);
            }
        }
        res.put("depts", depts);
        res.put("sales", sales);
        res.put("customers", customers);
        return AjaxResult.success(res);
    }

    @PostMapping("/getInitParams")
    public AjaxResult getInitParams() {
        Map<String,Object> res = new HashMap<>();
        List<Map<String,Object>> depts = new ArrayList<>();
        List<Map<String,Object>> sales = new ArrayList<>();
        List<Map<String,Object>> types = new ArrayList<>();
        Map<String,Object> subDept = new HashMap<>();
        subDept.put("value","all");
        subDept.put("label","(全部)");
        depts.add(subDept);
        Map<String,Object> subSal = new HashMap<>();
        subSal.put("value","all");
        subSal.put("label","(全部)");
        sales.add(subSal);
        Map<String,Object> subType = new HashMap<>();
        subType.put("value","all");
        subType.put("label","(全部)");
        types.add(subType);
        SimpleDateFormat yearsdf = new SimpleDateFormat("yyyy");
        Date date = new Date();
        String year = yearsdf.format(date);
        List<Map<String,Object>> deptsInfo = ymdCgBaseService.selectDepts(year);
        List<Map<String,Object>> salesInfo = ymdCgBaseService.selectSales(year);
        List<Map<String,Object>> typesInfo = ymdCgBaseService.selectTypes(year);
        if(deptsInfo.size()>0){
            for(Map<String,Object> dept:deptsInfo){
                depts.add(dept);
            }
        }
        if(salesInfo.size()>0){
            for(Map<String,Object> sal:salesInfo){
                sales.add(sal);
            }
        }
        if(typesInfo.size()>0){
            for(Map<String,Object> type:typesInfo){
                types.add(type);
            }
        }
        res.put("depts", depts);
        res.put("sales", sales);
        res.put("types", types);
        return AjaxResult.success(res);
    }

    public String getBaseUrl(){
        SimpleDateFormat yearsdf = new SimpleDateFormat("yyyy");
        SimpleDateFormat monthsdf = new SimpleDateFormat("MM");
        SimpleDateFormat daysdf = new SimpleDateFormat("dd");
        Date date = new Date();
        String year = yearsdf.format(date);
        String month = monthsdf.format(date);
        String day = daysdf.format(date);
        return "D:\\ruoyi\\uploadPath\\upload\\"+year+"\\"+month+"\\"+day+"\\";
    }

    @PostMapping("/importYmd")
    public AjaxResult importYmd(@RequestBody Map<String,Object> vo) {
        String path = getBaseUrl()+vo.get("nname").toString();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        File file = new File(path);
        List<Map<String,String>> res = new ArrayList<>();
        if (file.exists()) {
            if(path.endsWith(".xls")){
                try (FileInputStream fis = new FileInputStream(path);
                     Workbook workbook = new HSSFWorkbook(fis)) {
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sheet = workbook.getSheetAt(i);
                        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                            Row row = sheet.getRow(rowIndex);
                            if (row != null) {
                                SysCgzqImport zq = new SysCgzqImport();
                                Map<String, String> map = new HashMap<>();
                                Cell cell0 = row.getCell(0);
                                if(cell0!=null){
                                    map.put("brand",getCellValue(cell0,null));
                                }else{
                                    map.put("brand","");
                                }
                                Cell cell1 = row.getCell(1);
                                if(cell1!=null){
                                    map.put("spec",getCellValue(cell1,null));
                                }else {
                                    map.put("spec","");
                                }
                                Cell cell2 = row.getCell(2);
                                if(cell2!=null){
                                    map.put("code",getCellValue(cell2,null));
                                    zq.setPcode(getCellValue(cell2,null));
                                }else {
                                    map.put("code","");
                                }
                                Cell cell3 = row.getCell(3);
                                if(cell3!=null){
                                    map.put("cname",getCellValue(cell3,null));
                                }else{
                                    map.put("cname","");
                                }
                                Cell cell4 = row.getCell(4);
                                if(cell4!=null){
                                    map.put("ename",getCellValue(cell4,null));
                                }else {
                                    map.put("ename","");
                                }
                                Cell cell5 = row.getCell(5);
                                if(cell5!=null){
                                    if(getCellValue(cell5,null)!=null){
                                        map.put("qty",getCellValue(cell5,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                        zq.setQty(getCellValue(cell5,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                    }
                                }else {
                                    map.put("qty","");
                                }
                                Cell cell6 = row.getCell(6);
                                if(cell6!=null){
                                    map.put("changeqty",getCellValue(cell6,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                    zq.setChangeqty(getCellValue(cell6,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                }else {
                                    map.put("changeqty","");
                                }
                                if(cell5!=null){
                                    if(cell6==null){
                                        zq.setCls("qc");
                                    }
                                }
                                SysCgzqImport cgzqImport = zqImportService.selectSysCgzqImportByPcode(zq.getPcode());
                                if(cgzqImport!=null){
                                    zqImportService.updateSysCgzqImport(zq);
                                }else{
                                    zqImportService.insertSysCgzqImport(zq);
                                }
                                res.add(map);
                            }
                        }
                    }
                }catch (IOException e) {
                    e.printStackTrace();
                }
            }else{
                try (FileInputStream fis = new FileInputStream(new File(path));
                     Workbook workbook = new XSSFWorkbook(fis)) {
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sheet = workbook.getSheetAt(i);
                        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                            Row row = sheet.getRow(rowIndex);
                            if (row != null) {
                                SysCgzqImport zq = new SysCgzqImport();
                                Map<String, String> map = new HashMap<>();
                                Cell cell0 = row.getCell(0);
                                if(cell0!=null){
                                    map.put("brand",getCellValue(cell0,null));
                                }else{
                                    map.put("brand","");
                                }
                                Cell cell1 = row.getCell(1);
                                if(cell1!=null){
                                    map.put("spec",getCellValue(cell1,null));
                                }else {
                                    map.put("spec","");
                                }
                                Cell cell2 = row.getCell(2);
                                if(cell2!=null){
                                    map.put("code",getCellValue(cell2,null));
                                    zq.setPcode(getCellValue(cell2,null));
                                }else {
                                    map.put("code","");
                                }
                                Cell cell3 = row.getCell(3);
                                if(cell3!=null){
                                    map.put("cname",getCellValue(cell3,null));
                                }else{
                                    map.put("cname","");
                                }
                                Cell cell4 = row.getCell(4);
                                if(cell4!=null){
                                    map.put("ename",getCellValue(cell4,null));
                                }else {
                                    map.put("ename","");
                                }
                                Cell cell5 = row.getCell(5);
                                if(cell5!=null){
                                    map.put("qty",getCellValue(cell5,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                    zq.setQty(getCellValue(cell5,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                }else {
                                    map.put("qty","");
                                }
                                Cell cell6 = row.getCell(6);
                                if(cell6!=null){
                                    map.put("changeqty",getCellValue(cell6,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                    zq.setChangeqty(getCellValue(cell6,null).replaceAll("\\.0","").replaceAll("\\.00",""));
                                }else {
                                    map.put("changeqty","");
                                }
                                if(cell5!=null){
                                    if(cell6==null||getCellValue(cell6,null)==null||getCellValue(cell6,null).length()==0||getCellValue(cell6,null).equalsIgnoreCase("0")){
                                        zq.setCls("qc");
                                    }
                                }
                                SysCgzqImport cgzqImport = zqImportService.selectSysCgzqImportByPcode(zq.getPcode());
                                if(cgzqImport!=null){
                                    zqImportService.updateSysCgzqImport(zq);
                                }else{
                                    zqImportService.insertSysCgzqImport(zq);
                                }
                                res.add(map);
                            }
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return AjaxResult.success(res);
        }else {
            return AjaxResult.error("文件不存在");
        }
    }

    @PostMapping("/importQckc")
    public AjaxResult importQckc(@RequestBody Map<String,Object> vo) {
        String path = getBaseUrl()+vo.get("nname").toString();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String year = vo.get("year").toString();
        File file = new File(path);
        List<Map<String,String>> res = new ArrayList<>();
        if (file.exists()) {
            if(path.endsWith(".xls")){
                try (FileInputStream fis = new FileInputStream(path);
                     Workbook workbook = new HSSFWorkbook(fis)) {
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sheet = workbook.getSheetAt(i);
                        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                            Row row = sheet.getRow(rowIndex);
                            if (row != null) {
                                Map<String, String> map = new HashMap<>();
                                map.put("year",year);
                                Cell cell0 = row.getCell(0);
                                map.put("cname",getCellValue(cell0,null));
                                Cell cell1 = row.getCell(1);
                                map.put("ename",getCellValue(cell1,null));
                                Cell cell3 = row.getCell(3);
                                map.put("sku",getCellValue(cell3,null));
                                Cell cell2 = row.getCell(2);
                                if(getCellValue(cell2,null)!=null){
                                    SysYjgKc kc = new SysYjgKc();
                                    SysYjgKc kcCheck = new SysYjgKc();
                                    if (cell2 != null) {
                                        if(getCellValue(cell2,null)!=null){
                                            kc.setCode(getCellValue(cell2,null).replaceAll("\\.0",""));
                                            kcCheck.setCode(getCellValue(cell2,null).replaceAll("\\.0",""));
                                            map.put("code",getCellValue(cell2,null).replaceAll("\\.0",""));
                                        }
                                    }
                                    Cell cell4 = row.getCell(4);
                                    if (cell4 != null) {
                                        if(getCellValue(cell4,null)!=null&&!getCellValue(cell4,null).equalsIgnoreCase("0.0")){
                                            kc.setInventory(Long.parseLong(getCellValue(cell4,null).replaceAll("\\.0","")));
                                            map.put("inventory",getCellValue(cell4,null).replaceAll("\\.0",""));
                                        }
                                    }
                                    kc.setYear(year);
                                    kcCheck.setYear(year);
                                    res.add(map);
                                    List<SysYjgKc> kcCheckExists = kcService.selectSysYjgKcList(kc);
                                    if(kcCheckExists.size()>0){
                                        kcService.updateSysYjgKc(kc);
                                    }else{
                                        kcService.insertSysYjgKc(kc);
                                    }
                                }
                            }
                        }
                    }
                }catch (IOException e) {
                    e.printStackTrace();
                }
            }else{
                try (FileInputStream fis = new FileInputStream(new File(path));
                     Workbook workbook = new XSSFWorkbook(fis)) {
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sheet = workbook.getSheetAt(i);
                        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                            Row row = sheet.getRow(rowIndex);
                            if (row != null) {
                                Map<String, String> map = new HashMap<>();
                                map.put("year",year);
                                Cell cell0 = row.getCell(0);
                                map.put("cname",getCellValue(cell0,null));
                                Cell cell1 = row.getCell(1);
                                map.put("ename",getCellValue(cell1,null));
                                Cell cell3 = row.getCell(3);
                                map.put("sku",getCellValue(cell3,null));
                                Cell cell2 = row.getCell(2);
                                if(getCellValue(cell2,null)!=null){
                                    SysYjgKc kc = new SysYjgKc();
                                    SysYjgKc kcCheck = new SysYjgKc();
                                    if (cell2 != null) {
                                        if(getCellValue(cell2,null)!=null){
                                            kc.setCode(getCellValue(cell2,null).replaceAll("\\.0",""));
                                            kcCheck.setCode(getCellValue(cell2,null).replaceAll("\\.0",""));
                                            map.put("code",getCellValue(cell2,null).replaceAll("\\.0",""));
                                        }
                                    }
                                    Cell cell4 = row.getCell(4);
                                    if (cell4 != null) {
                                        if(getCellValue(cell4,null)!=null&&!getCellValue(cell4,null).equalsIgnoreCase("0.0")){
                                            kc.setInventory(Long.parseLong(getCellValue(cell4,null).replaceAll("\\.0","")));
                                            map.put("inventory",getCellValue(cell4,null).replaceAll("\\.0",""));
                                        }
                                    }
                                    kc.setYear(year);
                                    kcCheck.setYear(year);
                                    res.add(map);
                                    List<SysYjgKc> kcCheckExists = kcService.selectSysYjgKcList(kc);
                                    if(kcCheckExists.size()>0){
                                        kcService.updateSysYjgKc(kc);
                                    }else{
                                        kcService.insertSysYjgKc(kc);
                                    }
                                }
                            }
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return AjaxResult.success(res);
        }else {
            return AjaxResult.error("文件不存在");
        }
    }

    @PostMapping("/importWlda")
    public AjaxResult importWlda(@RequestBody Map<String,Object> vo) {
        String path = getBaseUrl()+vo.get("nname").toString();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        File file = new File(path);
        List<SysCgCpDim> res = new ArrayList<SysCgCpDim>();
        if (file.exists()) {
            if(path.endsWith(".xls")){
                try (FileInputStream fis = new FileInputStream(path);
                     Workbook workbook = new HSSFWorkbook(fis)) {
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sheet = workbook.getSheetAt(i);
                        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                            Row row = sheet.getRow(rowIndex);
                            if (row != null) {
                                SysCgCpDim cp = new SysCgCpDim();
                                SysCgCpDim cpCheck = new SysCgCpDim();
                                Cell cell2 = row.getCell(2);
                                if (cell2 != null) {
                                    String name = getCellValue(cell2,null);
                                    if(name!=null){
                                        Cell cell0 = row.getCell(0);
                                        if (cell0 != null) {
                                            if (getCellValue(cell0,null) != null) {
                                                cp.setYongyouCode(getCellValue(cell0,null).replaceAll(" ","").toUpperCase());
                                                cpCheck.setYongyouCode(getCellValue(cell0,null).replaceAll(" ","").toUpperCase());
                                            }
                                        }
                                        Cell cell1 = row.getCell(1);
                                        if (cell1 != null) {
                                            if(getCellValue(cell1,null)!=null){
                                                cp.setZzCode(getCellValue(cell1,null).replaceAll(" ","").toUpperCase());
                                            }
                                        }
                                        if (cell2 != null) {
                                            cp.setYongyouName(getCellValue(cell2,null));
                                        }
                                        Cell cell3 = row.getCell(3);
                                        if (cell3 != null) {
                                            cp.setCgNameCn(getCellValue(cell3,null));
                                        }
                                        Cell cell4 = row.getCell(4);
                                        if (cell4 != null) {
                                            if(getCellValue(cell4,null)!=null){
                                                cp.setMemCode(getCellValue(cell4,null).replaceAll("\\.0",""));
                                            }
                                        }
                                        Cell cell5 = row.getCell(5);
                                        if (cell5 != null) {
                                            cp.setType2(getCellValue(cell5,null));
                                        }
                                        Cell cell6 = row.getCell(6);
                                        if (cell6 != null) {
                                            cp.setType3( getCellValue(cell6,null));
                                        }
                                        Cell cell7 = row.getCell(7);
                                        if (cell7 != null) {
                                            cp.setSpec(getCellValue(cell7,null));
                                        }
                                        Cell cell8 = row.getCell(8);
                                        if (cell8 != null) {
                                            cp.setCpEnName(getCellValue(cell8,null));
                                        }
                                        Cell cell9 = row.getCell(9);
                                        if (cell9 != null) {
                                            cp.setSupEnName(getCellValue(cell9,null));
                                        }
                                        Cell cell10 = row.getCell(10);
                                        if (cell10 != null) {
                                            cp.setSupCnName(getCellValue(cell10,null));
                                        }
                                        Cell cell11 = row.getCell(11);
                                        if (cell11 != null) {
                                            cp.setType1(getCellValue(cell11,null));
                                        }
                                        res.add(cp);
                                        if(getCellValue(cell0,null)!=null){
                                            List<SysCgCpDim> checkExist = dimService.selectCpDimByYongyouCode(getCellValue(cell0,null).replaceAll(" ","").toUpperCase());
                                            if(checkExist.size()>0){
                                                dimService.updateSysCgCpDim(cp);
                                            }else{
                                                List<SysCgCpDim> checks = dimService.selectCpDimByYongyouName(getCellValue(cell2,null));
                                                if(checks.size()>0){
                                                    dimService.updateSysCgCpDim(cp);
                                                }else{
                                                    dimService.insertSysCgCpDim(cp);
                                                }
                                            }
                                        }else{
                                            List<SysCgCpDim> checks = dimService.selectCpDimByYongyouName(cp.getYongyouName());
                                            if(checks.size()>0){
                                                dimService.updateSysCgCpDim(cp);
                                            }else{
                                                dimService.insertSysCgCpDim(cp);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
                return AjaxResult.success(res);
            }else{
                try (FileInputStream fis = new FileInputStream(new File(path));
                     Workbook workbook = new XSSFWorkbook(fis)) {
                    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                        Sheet sheet = workbook.getSheetAt(i);
                        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                            Row row = sheet.getRow(rowIndex);
                            if (row != null) {
                                SysCgCpDim cp = new SysCgCpDim();
                                SysCgCpDim cpCheck = new SysCgCpDim();
                                Cell cell2 = row.getCell(2);
                                if (cell2 != null) {
                                    String name = getCellValue(cell2,null);
                                    if(name!=null){
                                        Cell cell0 = row.getCell(0);
                                        if (cell0 != null) {
                                            if (getCellValue(cell0,null) != null) {
                                                cp.setYongyouCode(getCellValue(cell0,null).replaceAll(" ","").toUpperCase());
                                                cpCheck.setYongyouCode(getCellValue(cell0,null).replaceAll(" ","").toUpperCase());
                                            }
                                        }
                                        Cell cell1 = row.getCell(1);
                                        if (cell1 != null) {
                                            if(getCellValue(cell1,null)!=null){
                                                cp.setZzCode(getCellValue(cell1,null).replaceAll(" ","").toUpperCase());
                                            }
                                        }
                                        if (cell2 != null) {
                                            cp.setYongyouName(getCellValue(cell2,null));
                                        }
                                        Cell cell3 = row.getCell(3);
                                        if (cell3 != null) {
                                            cp.setCgNameCn(getCellValue(cell3,null));
                                        }
                                        Cell cell4 = row.getCell(4);
                                        if (cell4 != null) {
                                            if(getCellValue(cell4,null)!=null){
                                                cp.setMemCode(getCellValue(cell4,null).replaceAll("\\.0",""));
                                            }
                                        }
                                        Cell cell5 = row.getCell(5);
                                        if (cell5 != null) {
                                            cp.setType2(getCellValue(cell5,null));
                                        }
                                        Cell cell6 = row.getCell(6);
                                        if (cell6 != null) {
                                            cp.setType3( getCellValue(cell6,null));
                                        }
                                        Cell cell7 = row.getCell(7);
                                        if (cell7 != null) {
                                            cp.setSpec(getCellValue(cell7,null));
                                        }
                                        Cell cell8 = row.getCell(8);
                                        if (cell8 != null) {
                                            cp.setCpEnName(getCellValue(cell8,null));
                                        }
                                        Cell cell9 = row.getCell(9);
                                        if (cell9 != null) {
                                            cp.setSupEnName(getCellValue(cell9,null));
                                        }
                                        Cell cell10 = row.getCell(10);
                                        if (cell10 != null) {
                                            cp.setSupCnName(getCellValue(cell10,null));
                                        }
                                        Cell cell11 = row.getCell(11);
                                        if (cell11 != null) {
                                            cp.setType1(getCellValue(cell11,null));
                                        }
                                        res.add(cp);
                                        if(getCellValue(cell0,null)!=null){
                                            List<SysCgCpDim> checkExist = dimService.selectCpDimByYongyouCode(getCellValue(cell0,null).replaceAll(" ","").toUpperCase());
                                            if(checkExist.size()>0){
                                                dimService.updateSysCgCpDim(cp);
                                            }else{
                                                List<SysCgCpDim> checks = dimService.selectCpDimByYongyouName(cp.getYongyouName());
                                                if(checks.size()>0){
                                                    dimService.updateSysCgCpDim(cp);
                                                }else{
                                                    dimService.insertSysCgCpDim(cp);
                                                }
                                            }
                                        }else{
                                            List<SysCgCpDim> checks = dimService.selectCpDimByYongyouName(cp.getYongyouName());
                                            if(checks.size()>0){
                                                dimService.updateSysCgCpDim(cp);
                                            }else{
                                                dimService.insertSysCgCpDim(cp);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
                return AjaxResult.success(res);
            }
        }else {
            return AjaxResult.error("文件不存在");
        }
    }

    @PostMapping("/importCgzm")
    public AjaxResult importCgzm(@RequestBody Map<String,Object> vo) {
        String path = getBaseUrl()+vo.get("nname").toString();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        File file = new File(path);
        List<SysCgYjgZm> res = new ArrayList<SysCgYjgZm>();
        if (file.exists()) {
            if(path.endsWith(".xls")){
                try (FileInputStream fis = new FileInputStream(path);
                     Workbook workbook = new HSSFWorkbook(fis)) {
                    // 获取第一个工作表
                    Sheet sheet = workbook.getSheetAt(0);
                    // 遍历行
                    for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row != null) {
                            SysCgYjgZm zz = new SysCgYjgZm();
                            SysCgYjgZm zzCheck = new SysCgYjgZm();
                            Cell cell0 = row.getCell(0);
                            if (cell0 != null) {
                                zzCheck.setFhDate(getCellValue(cell0,sdf));
                                zz.setFhDate(getCellValue(cell0,sdf));
                            }
                            Cell cell1 = row.getCell(1);
                            if (cell1 != null) {
                                zzCheck.setSpec(getCellValue(cell1,null));
                                zz.setSpec(getCellValue(cell1,null));
                            }
                            Cell cell2 = row.getCell(2);
                            if (cell2 != null) {
                                zzCheck.setCusType(getCellValue(cell2,null));
                                zz.setCusType(getCellValue(cell2,null));
                            }
                            Cell cell3 = row.getCell(3);
                            if (cell3 != null) {
                                zz.setPrdName(getCellValue(cell3,null));
                            }
                            Cell cell4 = row.getCell(4);
                            if (cell4 != null) {
                                zzCheck.setPrdEnName(getCellValue(cell4,null));
                                zz.setPrdEnName(getCellValue(cell4,null));
                            }
                            Cell cell5 = row.getCell(5);
                            if (cell5 != null) {
                                zz.setPrdCnName(getCellValue(cell5,null));
                            }
                            Cell cell6 = row.getCell(6);
                            if (cell6 != null) {
                                zzCheck.setMemCode(getCellValue(cell6,null).replaceAll("\\.0",""));
                                zz.setMemCode(getCellValue(cell6,null).replaceAll("\\.0",""));
                            }
                            Cell cell7 = row.getCell(7);
                            if (cell7 != null) {
                                zzCheck.setYongyouCode(getCellValue(cell7,null));
                                zz.setYongyouCode(getCellValue(cell7,null));
                            }
                            Cell cell9 = row.getCell(9);
                            if (cell9 != null) {
                                zz.setArrZmNums(getCellValue(cell9,null).replaceAll("\\.0",""));
                            }
                            Cell cell12 = row.getCell(12);
                            if (cell12 != null) {
                                zz.setArrZmDate(getCellValue(cell12,sdf));
                            }
                            List<SysCgYjgZm> checkExist = zmService.selectSysCgYjgZmList(zzCheck);
                            if(checkExist.size()>0){
                                zmService.updateByParams(zz);
                            }else{
                                zmService.insertSysCgYjgZm(zz);
                            }
                            res.add(zz);
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }else{
                try (FileInputStream fis = new FileInputStream(new File(path));
                     Workbook workbook = new XSSFWorkbook(fis)) {
                    // 获取第一个工作表
                    Sheet sheet = workbook.getSheetAt(0);
                    // 遍历行
                    for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row != null) {
                            SysCgYjgZm zz = new SysCgYjgZm();
                            SysCgYjgZm zzCheck = new SysCgYjgZm();
                            Cell cell0 = row.getCell(0);
                            if (cell0 != null) {
                                zzCheck.setFhDate(getCellValue(cell0,sdf));
                                zz.setFhDate(getCellValue(cell0,sdf));
                            }
                            Cell cell1 = row.getCell(1);
                            if (cell1 != null) {
                                zzCheck.setSpec(getCellValue(cell1,null));
                                zz.setSpec(getCellValue(cell1,null));
                            }
                            Cell cell2 = row.getCell(2);
                            if (cell2 != null) {
                                zzCheck.setCusType(getCellValue(cell2,null));
                                zz.setCusType(getCellValue(cell2,null));
                            }
                            Cell cell3 = row.getCell(3);
                            if (cell3 != null) {
                                zz.setPrdName(getCellValue(cell3,null));
                            }
                            Cell cell4 = row.getCell(4);
                            if (cell4 != null) {
                                zzCheck.setPrdEnName(getCellValue(cell4,null));
                                zz.setPrdEnName(getCellValue(cell4,null));
                            }
                            Cell cell5 = row.getCell(5);
                            if (cell5 != null) {
                                zz.setPrdCnName(getCellValue(cell5,null));
                            }
                            Cell cell6 = row.getCell(6);
                            if (cell6 != null) {
                                zzCheck.setMemCode(getCellValue(cell6,null).replaceAll("\\.0",""));
                                zz.setMemCode(getCellValue(cell6,null).replaceAll("\\.0",""));
                            }
                            Cell cell7 = row.getCell(7);
                            if (cell7 != null) {
                                zzCheck.setYongyouCode(getCellValue(cell7,null));
                                zz.setYongyouCode(getCellValue(cell7,null));
                            }
                            Cell cell9 = row.getCell(9);
                            if (cell9 != null) {
                                zz.setArrZmNums(getCellValue(cell9,null).replaceAll("\\.0",""));
                            }
                            Cell cell12 = row.getCell(12);
                            if (cell12 != null) {
                                zz.setArrZmDate(getCellValue(cell12,sdf));
                            }
                            List<SysCgYjgZm> checkExist = zmService.selectSysCgYjgZmList(zzCheck);
                            if(checkExist.size()>0){
                                zmService.updateByParams(zz);
                            }else{
                                zmService.insertSysCgYjgZm(zz);
                            }
                            res.add(zz);
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return AjaxResult.success(res);
        } else {
            return AjaxResult.error("文件不存在");
        }
    }

    @PostMapping("/importCgzz")
    public AjaxResult importCgzz(@RequestBody Map<String,Object> vo) {
        String path = getBaseUrl()+vo.get("nname").toString();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        File file = new File(path);
        List<SysCgYjgZz> res = new ArrayList<SysCgYjgZz>();
        if (file.exists()) {
            if(path.endsWith(".xls")){
                try (FileInputStream fis = new FileInputStream(path);
                     Workbook workbook = new HSSFWorkbook(fis)) {
                    // 获取第一个工作表
                    Sheet sheet = workbook.getSheetAt(0);
                    // 遍历行
                    for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row != null) {
                            SysCgYjgZz zz = new SysCgYjgZz();
                            SysCgYjgZz zzCheck = new SysCgYjgZz();

                            Cell cell0 = row.getCell(0);
                            if (cell0 != null) {
                                zzCheck.setFhDate(getCellValue(cell0,sdf));
                                zz.setFhDate(getCellValue(cell0,sdf));
                            }
                            Cell cell1 = row.getCell(1);
                            if (cell1 != null) {
                                zzCheck.setZzCgDate(getCellValue(cell1,sdft));
                                zz.setZzCgDate(getCellValue(cell1,sdft));
                            }
                            Cell cell4 = row.getCell(4);
                            if (cell4 != null) {
                                zzCheck.setMemCode(getCellValue(cell4,sdft).replaceAll("\\.0",""));
                                zz.setMemCode(getCellValue(cell4,null).replaceAll("\\.0",""));
                            }
                            Cell cell5 = row.getCell(5);
                            if (cell5 != null) {
                                zz.setPrdCnName(getCellValue(cell5,null));
                            }
                            Cell cell6 = row.getCell(6);
                            if (cell6 != null) {
                                zzCheck.setPrdEnName(getCellValue(cell6,null));
                                zz.setPrdEnName(getCellValue(cell6,null));
                            }
                            Cell cell7 = row.getCell(7);
                            if (cell7 != null) {
                                zzCheck.setYongyouCode(getCellValue(cell7,null));
                                zz.setYongyouCode(getCellValue(cell7,null));
                            }
                            Cell cell12 = row.getCell(12);
                            if (cell12 != null) {
                                if(getCellValue(cell12,null)!=null&&getCellValue(cell12,null)!="null"){
                                    zz.setArrZzNums(getCellValue(cell12,null).replaceAll("\\.0",""));
                                }else{
                                    zz.setArrZzNums(getCellValue(cell12,null));
                                }
                            }
                            Cell cell13 = row.getCell(13);
                            if(cell13!=null){
                                if(getCellValue(cell13,null)!=null&&getCellValue(cell13,null)!="null"){
                                    zz.setKtzZzNums(getCellValue(cell13,null).replaceAll("\\.0",""));
                                }else{
                                    zz.setKtzZzNums(getCellValue(cell13,null));
                                }
                            }
                            Cell cell14 = row.getCell(14);
                            if(cell14!=null){
                                zzCheck.setArrZzDate(getCellValue(cell14,sdft));
                                zz.setArrZzDate(getCellValue(cell14,sdft));
                            }
                            List<SysCgYjgZz> checkExist = zzService.selectSysCgYjgZzList(zzCheck);
                            if(checkExist.size()>0){
                                zzService.updateByParams(zz);
                            }else{
                                zzService.insertSysCgYjgZz(zz);
                            }
                            res.add(zz);
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }else{
                try (FileInputStream fis = new FileInputStream(new File(path));
                     Workbook workbook = new XSSFWorkbook(fis)) {
                    // 获取第一个工作表
                    Sheet sheet = workbook.getSheetAt(0);
                    // 遍历行
                    for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                        Row row = sheet.getRow(rowIndex);
                        if (row != null) {
                            SysCgYjgZz zz = new SysCgYjgZz();
                            SysCgYjgZz zzCheck = new SysCgYjgZz();

                            Cell cell0 = row.getCell(0);
                            if (cell0 != null) {
                                zzCheck.setFhDate(getCellValue(cell0,sdf));
                                zz.setFhDate(getCellValue(cell0,sdf));
                            }
                            Cell cell1 = row.getCell(1);
                            if (cell1 != null) {
                                zzCheck.setZzCgDate(getCellValue(cell1,sdft));
                                zz.setZzCgDate(getCellValue(cell1,sdft));
                            }
                            Cell cell4 = row.getCell(4);
                            if (cell4 != null) {
                                zzCheck.setMemCode(getCellValue(cell4,sdft).replaceAll("\\.0",""));
                                zz.setMemCode(getCellValue(cell4,null).replaceAll("\\.0",""));
                            }
                            Cell cell5 = row.getCell(5);
                            if (cell5 != null) {
                                zz.setPrdCnName(getCellValue(cell5,null));
                            }
                            Cell cell6 = row.getCell(6);
                            if (cell6 != null) {
                                zzCheck.setPrdEnName(getCellValue(cell6,null));
                                zz.setPrdEnName(getCellValue(cell6,null));
                            }
                            Cell cell7 = row.getCell(7);
                            if (cell7 != null) {
                                zzCheck.setYongyouCode(getCellValue(cell7,null));
                                zz.setYongyouCode(getCellValue(cell7,null));
                            }
                            Cell cell12 = row.getCell(12);
                            if (cell12 != null) {
                                if(getCellValue(cell12,null)!=null&&getCellValue(cell12,null)!="null"){
                                    zz.setArrZzNums(getCellValue(cell12,null).replaceAll("\\.0",""));
                                }else{
                                    zz.setArrZzNums(getCellValue(cell12,null));
                                }
                            }
                            Cell cell13 = row.getCell(13);
                            if(cell13!=null){
                                if(getCellValue(cell13,null)!=null&&getCellValue(cell13,null)!="null"){
                                    zz.setKtzZzNums(getCellValue(cell13,null).replaceAll("\\.0",""));
                                }else{
                                    zz.setKtzZzNums(getCellValue(cell13,null));
                                }
                            }
                            Cell cell14 = row.getCell(14);
                            if(cell14!=null){
                                zzCheck.setArrZzDate(getCellValue(cell14,sdft));
                                zz.setArrZzDate(getCellValue(cell14,sdft));
                            }
                            List<SysCgYjgZz> checkExist = zzService.selectSysCgYjgZzList(zzCheck);
                            if(checkExist.size()>0){
                                zzService.updateByParams(zz);
                            }else{
                                zzService.insertSysCgYjgZz(zz);
                            }
                            res.add(zz);
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return AjaxResult.success(res);
        } else {
            return AjaxResult.error("文件不存在");
        }
    }

    public String getCellValue(Cell cell,SimpleDateFormat sdf) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if(date!=null){
                        return sdf.format(date);
                    }else{
                        return ""+cell.getDateCellValue();
                    }
                } else {
                    return ""+cell.getNumericCellValue();
                }
            case BOOLEAN:
                return ""+cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }
}
